package edu.faytechcc.csc251.db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

/**
 *
 * @author kent
 */
public class PopulateMTGDB {

    public static void main(String[] args)
            throws Exception {
        final String DB_URL =
                "jdbc:derby:MTGCardDB";

        try {
            //Create a connection to the database.
            Connection conn = DriverManager.getConnection(DB_URL);
            //Create a Statement object
            Statement stmt = conn.createStatement();
            Scanner scanner = new Scanner(new File("MTGCards(fixed).csv"));
            String line;
            String name;
            String setName;
            String id;
            String type;
            String power;
            String toughness;
            String manacost;
            String cmc;
            String color;
            String rarity;
            String ability;
            System.out.println("Connection est, building DB...");
            Thread.sleep(5000);
            int i = 0;
            int length = 0;
            while (scanner.hasNextLine()) {
                try {
                    line = scanner.nextLine();
                    line = line.replace("£", " ");
                    line = line.replace("#_(", "(");
                    line = line.replace(")_#", ")");
                    String[] cardStrings;
                    cardStrings = line.split("\\|");
                    length = cardStrings.length;
                    name = cardStrings[0];
                    name = name.replace("'", "''");
                    setName = cardStrings[1];
                    setName = setName.replace("'", "''");
                    id = cardStrings[2];
                    type = cardStrings[3];
                    type = type.replace("'", "''");
                    power = cardStrings[4];
                    if (power.equals("")) {
                        power = "''";
                    } else {
                        power = "'" + power + "'";
                    }
                    toughness = cardStrings[5];
                    if (toughness.equals("")) {
                        toughness = "''";
                    } else {
                        toughness = "'" + toughness + "'";
                    }
                    manacost = cardStrings[6];
                    if (manacost.equals("")) {
                        manacost = "''";
                    }
                    cmc = cardStrings[7];
                    if (cmc.equals("")) {
                        cmc = "''";
                    } else {
                        cmc = "'" + cmc + "'";
                    }
                    color = cardStrings[8];
                    if (color.equals("")) {
                        color = "''";
                    }
                    rarity = cardStrings[9];
                    if (rarity.equals("")) {
                        rarity = "''";
                    }

                    String sqlStatement;
                    if (cardStrings.length == 10) {
                        sqlStatement = "INSERT INTO Card "
                                + "(Name, SetName, ID, Type, Power, Toughness, Manacost, "
                                + "ConvertedManacost, Color, Rarity) "
                                + "VALUES ('" + name + "', '" + setName + "', '" + id + "', "
                                + "'" + type + "', " + power + ", " + toughness + ", '" + manacost
                                + "', " + cmc + ", '" + color + "', '" + rarity + "')";
                    } else {
                        ability = cardStrings[10];
                        ability = ability.replace("'", "''");
                        sqlStatement = "INSERT INTO Card "
                                + "(Name, SetName, ID, Type, Power, Toughness, Manacost, "
                                + "ConvertedManacost, Color, Rarity, Ability) "
                                + "VALUES ('" + name + "', '" + setName + "', '" + id + "', "
                                + "'" + type + "', " + power + ", " + toughness + ", '" + manacost
                                + "', " + cmc + ", '" + color + "', '" + rarity + "', '"
                                + ability + "')";
                    }
                    int rows = stmt.executeUpdate(sqlStatement);
                    System.out.println((i) + "Done");
                } catch (Exception ex) {

                    System.out.println("ERROR: " + ex.getMessage());
                    System.out.println(length);
                    System.out.println(i);
                }
                i++;
            }
            System.out.println("Database updated.");
        } catch (Exception ex) {
            System.out.println("ERROR: " + ex.getMessage());
        }

    }
}
